#!/usr/bin/python
# -*- coding: utf-8 -*-
import pymysql
import pprint
import htmlentitydefs
import re
from datetime import datetime

# TODO: Chance encoding from ascii to the somewhat more modern utf-8
pp = pprint.PrettyPrinter(indent=2)

PARENT_CATEGORY_ID = 2
PARENT_CATEGORY_DEPTH = 1

# redcross group id to vanilla category id function
rid2vid = lambda i: i*1000

def slugify(text):
    ret = ""
    for c in text.lower():
        try:
            ret += htmlentitydefs.codepoint2name[ord(c)]
        except:
            ret += c
    ret = re.sub("([a-zA-Z])(uml|acute|grave|circ|tilde|cedil)(brvbar|yen|cedil)*", r"\1", ret)
    ret = re.sub("\W", " ", ret)
    ret = re.sub(" +", "-", ret)
    return ret.strip().lower()

print "Sync started at %s\n" %datetime.now()

redcross = pymysql.connect(host='localhost', port=3306, user='trkh', passwd='qdw5nef2f7uRqELL', db='shiftmanagement', charset='utf8')
vanilla = pymysql.connect(host='localhost', port=3306, user='trkh', passwd='qdw5nef2f7uRqELL', db='shiftmanagement', charset='utf8')

def fetch_redcross_groups():
    cur = redcross.cursor()
    cur.execute("SELECT id, name " \
                "FROM redcross_group " \
                "WHERE is_forum_group = 1");
    
    result = {}

    for row in cur:
        group_id = int(row[0])
        group_name = row[1]

        result[group_id] = {'name': group_name}
    cur.close()
    return result

#print "fetch_redcross_groups"
#pp.pprint(fetch_redcross_groups())


def fetch_redcross_group_members():
    cur = redcross.cursor()
    cur.execute("SELECT rm.id, rg.id, rg.name " \
                "FROM redcross_member AS rm " \
                "LEFT JOIN redcross_user AS ru ON ru.member_id=rm.id " \
                "LEFT JOIN redcross_user_group AS rug ON rug.user_id=ru.id " \
                "LEFT JOIN redcross_group AS rg ON rg.id=rug.group_id " \
                "WHERE rg.id IS NOT NULL " \
                "AND ru.is_confirmed = 1 " \
                "AND rg.is_forum_group = 1")

    result = {}
    for row in cur:
        member_id = int(row[0])
        group_id = int(row[1])
        group_name = row[2]

        if group_id not in result:
            result[group_id] = {'name': group_name, 'members': []}

        result[group_id]['members'].append(member_id)
    cur.close()
    return result

#print "\nfetch_redcross_group_members"
#pp.pprint(fetch_redcross_group_members())


def fetch_redcross_members():
    cur = redcross.cursor()
    cur.execute("SELECT rm.id, ru.username, re.address, rm.first_name, rm.last_name " \
                "FROM redcross_member AS rm " \
                "LEFT JOIN redcross_user AS ru ON ru.member_id=rm.id " \
                "LEFT JOIN redcross_email AS re ON re.member_id=rm.id " \
                "WHERE ru.id IS NOT NULL " \
                "AND ru.is_confirmed = 1 " \
                "AND re.is_main = 1")

    result = {}
    for row in cur:
        member_id = int(row[0])
        username = row[1]
        email = row[2]
        first_name = row[3]
        last_name = row[4]
        full_name = "%s %s" % (first_name, last_name)

        result[member_id] = {'username': username, 'email': email, 'first_name': first_name, 'last_name': last_name, 'full_name': full_name}
    cur.close()
    return result

#print "\nfetch_redcross_members"
#pp.pprint(fetch_redcross_members())


def fetch_vanilla_users():
    cur = vanilla.cursor()
    cur.execute("SELECT UA.ForeignUserKey, U.UserID, U.Name, U.Email " \
                "FROM GDN_User AS U " \
                "LEFT JOIN GDN_UserAuthentication AS UA ON U.UserID=UA.UserID " \
                "WHERE UA.ForeignUserKey IS NOT NULL")

    result = {}
    for row in cur:
        redcross_member_id = int(row[0])
        user_id = int(row[1])
        full_name = row[2]
        email = row[3]

        if redcross_member_id not in result.keys():
            result[redcross_member_id] = {'user_id': user_id, 'full_name': full_name, 'email': email}
    cur.close()
    return result

#print "\nfetch_vanilla_users"
#pp.pprint(fetch_vanilla_users())

def member_exists_in_vanilla(redcross_member_id):
    cur = vanilla.cursor()
    cur.execute("SELECT COUNT(UA.ForeignUserKey) " \
                "FROM GDN_UserAuthentication AS UA " \
                "WHERE UA.ForeignUserKey = %i" % int(redcross_member_id))

    return int(cur.fetchone()[0]) > 0

def fetch_vanilla_categories():
    cur = vanilla.cursor()
    cur.execute("SELECT C.CategoryID, C.Name, C.TreeLeft, C.TreeRight, C.Depth " \
                "FROM GDN_Category AS C")

    result = {}
    for row in cur:
        category_id = int(row[0])
        category_name = row[1]
        left = row[2]
        right = row[3]
        depth = row[4]

        if category_id not in result:
            result[category_id] = {'name': category_name, 'left': left, 'right': right, 'depth': depth}
    cur.close()
    return result

#print "\nfetch_vanilla_categories"
#pp.pprint(fetch_vanilla_categories())


def update_user_info():
    print "-------------------"
    print " Updating users..."
    print "-------------------"
    redcross_members = fetch_redcross_members()
    vanilla_users  = fetch_vanilla_users()

    no_account = []

    for member_id, attr in redcross_members.items():
        #name = attr['full_name'].encode('ascii', 'replace')
        #name = attr['full_name'].encode('utf-8')
        name = attr['full_name']
        email = attr['email']

        if member_id in vanilla_users:
            user_id = vanilla_users[member_id]['user_id']
            print "Updating member '%s' with id %i" % (name.encode('ascii', 'replace'), user_id)

            cur = vanilla.cursor()
            cur.execute("UPDATE GDN_User " \
                        "SET Name = '%s', " \
                        "    Email = '%s' " \
                        "WHERE UserID = %i" % (name, email, user_id))
            cur.close()
            print "Member '%s' updated." % (name.encode('ascii', 'replace'))
        else:
            no_account.append(name.encode('ascii', 'replace'))

    print "Finished updating users\n"

    if len(no_account) > 0:
        print "The following members could not be updated " \
              "due to not having a forum account: %s\n" % (", ".join(no_account))


def fetch_vanilla_roles():
    cur = vanilla.cursor()
    cur.execute("SELECT RoleID, Name " \
                "FROM GDN_Role")

    roles = {}
    for row in cur:
        id = int(row[0])
        name = row[1]
        roles[id] = {'name': name}

    cur.close()
    return roles

#print "fetch_vanilla_roles"
#pp.pprint(fetch_vanilla_roles())

# todo: don't know if we need this
def fetch_vanilla_permissions():
    cur = vanilla.cursor()
    cur.execute("SELECT RoleID, PermissionID, JunctionID " \
                "FROM GDN_Permission")

    permissions = {}
    for row in cur:
        if row[2] is not None:
            role_id = int(row[0])
            permission_id = int(row[1])
            junction_id = int(row[2])

            if junction_id not in permissions:
                permissions[junction_id] = []

            permissions[junction_id].append({'id': permission_id, 'role_id': role_id})
    cur.close()
    return permissions

#print "fetch_vanilla_permissions"
#pp.pprint(fetch_vanilla_permissions())

def update_roles():
    print "-------------------"
    print " Updating roles... "
    print "-------------------"

    redcross_groups = fetch_redcross_groups()
    vanilla_roles = fetch_vanilla_roles()

    for group_id, attr in redcross_groups.items():
        role_id = rid2vid(group_id)
        name = attr['name']
        description = 'Synkronisert fra vaktukallingssystemet'

        cur = vanilla.cursor()
        
        if role_id in vanilla_roles:
            print "Updating role '%s' with id %i" % (name, role_id)
            cur.execute("UPDATE GDN_Role " \
                        "SET Name='%s' " \
                        "WHERE RoleID=%i" % (name, role_id))
            print "Role '%s' updated" % name
        else:
            print "Inserting role '%s' with id %i" % (name, role_id)
            cur.execute("INSERT INTO GDN_Role " \
                        "(RoleID, Name, Description, Deletable, CanSession) " \
                        "VALUES (%i, '%s', '%s', 1, 1)" % (role_id, name, description))
            print "Role '%s' inserted with id %i" % (name, role_id)
        cur.close()
    print "Finished updating roles\n"
        
def update_categories():
    print "------------------------"
    print " Updating categories..."
    print "------------------------"
    redcross_groups = fetch_redcross_groups()

    for group_id, attr in redcross_groups.items():
        # We have to fetch this on each iteration in order be sure
        # that we have correct left/right values for the parent.
        # Better safe than sorry...
        vanilla_categories = fetch_vanilla_categories()

        category_id = rid2vid(group_id)
        name = attr['name']
        slug = slugify(attr['name'])
        parent = vanilla_categories[PARENT_CATEGORY_ID]

        cur = vanilla.cursor()

        if category_id in vanilla_categories:
            print "Updating category '%s' with id %i" % (name, category_id)
            cur.execute("UPDATE GDN_Category " \
                        "SET ParentCategoryID = %i, " \
                        "    Depth = %i, " \
                        "    Name = '%s', " \
                        "    UrlCode = '%s', " \
                        "    PermissionCategoryID = %i " \
                        "WHERE CategoryID = %i" % (PARENT_CATEGORY_ID, parent['depth']+1, name, slug, category_id, category_id))
            print "Category '%s' updated" % (attr['name'])
        else:
            print "Inserting category '%s' with id %i" % (name, category_id)
            # Incr +2 left/right values of all left/right > parent's left
            cur.execute("UPDATE GDN_Category " \
                        "SET TreeLeft=TreeLeft+2 " \
                        "WHERE TreeLeft > %i" % parent['left'])
            cur.execute("UPDATE GDN_Category " \
                        "SET TreeRight=TreeRight+2 " \
                        "WHERE TreeRight > %i" % parent['left'])

            # insert new category in the empty space we created
            cur.execute("INSERT INTO GDN_Category " \
                        "(CategoryID, ParentCategoryID, Depth, Name, UrlCode, TreeLeft, TreeRight, PermissionCategoryID) " \
                        "VALUES (%i, %i, %i, '%s', '%s', %i, %i, %i)" % (category_id, PARENT_CATEGORY_ID, parent['depth']+1, name, slug, parent['left']+1, parent['left']+2, category_id))
            print "Category '%s' inserted with id %i" % (name, category_id)
        cur.close()
    print "Finished updating categories\n"

def update_permissions():
    print "-------------------------"
    print " Updating permissions..."
    print "-------------------------"
    redcross_groups = fetch_redcross_groups()
    vanilla_roles = fetch_vanilla_roles()

    cur = vanilla.cursor()

    for group_id, attr in redcross_groups.items():
        print "Updating permissions for category '%s' with id %i" % (attr['name'], group_id)
        
        junction_id = rid2vid(group_id)
        junction_table = 'Category'
        junction_column = 'PermissionCategoryID'

        permissions = [
                {   # Administrator
                    'role_id': 16,
                    'junction_id': junction_id,
                    'd_view': 1, 'd_add': 1, 'd_edit': 1, 'd_announce': 1,
                    'd_sink': 1, 'd_close': 1, 'd_delete': 1,
                    'c_add': 1, 'c_edit': 1, 'c_delete': 1
                },
                {   # Moderator
                    'role_id': 32,
                    'junction_id': junction_id,
                    'd_view': 1, 'd_add': 1, 'd_edit': 1, 'd_announce': 1,
                    'd_sink': 1, 'd_close': 1, 'd_delete': 1,
                    'c_add': 1, 'c_edit': 1, 'c_delete': 1
                },
                {
                    'role_id': junction_id,
                    'junction_id': junction_id,
                    'd_view': 1, 'd_add': 1, 'd_edit': 0, 'd_announce': 0,
                    'd_sink': 0, 'd_close': 0, 'd_delete': 0,
                    'c_add': 1, 'c_edit': 0, 'c_delete': 0
                },
        ]

        # delete all related permissions first
        print "Deleting permissions for category '%s' with id %i" % (attr['name'], group_id)
        cur.execute("DELETE FROM GDN_Permission " \
                    "WHERE JunctionID = %i" % (junction_id))
        cur.execute("DELETE FROM GDN_Permission " \
                    "WHERE JunctionID = -1 AND RoleID = %i" % (junction_id))
        print "Permissions deleted"

        # and then unblock some roles
        for p in permissions:
            print "Creating new permissions for category '%s' with id %i" % (attr['name'], group_id)
            cur.execute("INSERT INTO GDN_Permission " \
                        "(RoleID, JunctionTable, JunctionColumn, JunctionID, " \
                        " `Vanilla.Discussions.View`, `Vanilla.Discussions.Add`, `Vanilla.Discussions.Edit`, " \
                        " `Vanilla.Discussions.Announce`, `Vanilla.Discussions.Sink`, `Vanilla.Discussions.Close`, " \
                        " `Vanilla.Discussions.Delete`, `Vanilla.Comments.Add`, `Vanilla.Comments.Edit`, " \
                        " `Vanilla.Comments.Delete`) " \
                        "VALUES (%i, '%s', '%s', %i, %i, %i, %i, %i, %i, %i, %i, %i, %i, %i)" % (p['role_id'], junction_table, junction_column, p['junction_id'], p['d_view'], p['d_add'], p['d_edit'], p['d_announce'], p['d_sink'], p['d_close'], p['d_delete'], p['c_add'], p['c_edit'], p['c_delete']))
            print "Permissions created"

        cur.execute("INSERT INTO GDN_Permission " \
                    "(RoleID, JunctionTable, JunctionColumn, JunctionID, " \
                    " `Vanilla.Discussions.View`, `Vanilla.Discussions.Add`) " \
                    "VALUES (%i, '%s', '%s', -1, 1, 1)" % (junction_id, junction_table, junction_column))
    cur.close()
    print "Finished updating permissions"

def update_user_roles():
    print "------------------------"
    print " Updating user roles..."
    print "------------------------"

    users = fetch_vanilla_users()
    groups = fetch_redcross_group_members()

    no_account = []
    cur = vanilla.cursor()
    for group_id, attr in groups.items():
        role_id = rid2vid(group_id)

        print "Updating users for role with id %i" % role_id 

        cur.execute("DELETE FROM GDN_UserRole " \
                    "WHERE RoleID = %i" % role_id)

        for member_id in attr['members']:
            if member_id in users:
                user_id = users[member_id]['user_id']
                print "Adding member with id %i to role" % user_id
                cur.execute("INSERT INTO GDN_UserRole " \
                            "(UserID, RoleID) " \
                            "VALUES (%i, %i)" % (user_id, role_id))
                print "Member added."
            else:
                no_account.append(str(member_id))
    cur.close()
    print "Finished updating user roles.\n"

    if len(no_account) > 0:
        print "The following member ids could not be added " \
              "to the user-role table due to not having a forum account: %s\n" % (", ".join(no_account))

def delete_permission_cache():
    print "------------------------------"
    print " Deleting permission cache..."
    print "------------------------------"
    redcross_members = fetch_redcross_members()
    vanilla_users  = fetch_vanilla_users()

    no_account = []

    for member_id, attr in redcross_members.items():
        #name = attr['full_name'].encode('ascii', 'replace')
        #name = attr['full_name'].encode('utf-8')
        name = attr['full_name']

        if member_id in vanilla_users:
            user_id = vanilla_users[member_id]['user_id']
            print "Deleting permission cache for user '%s' with id %i" % (name.encode('ascii', 'replace'), user_id)

            cur = vanilla.cursor()
            cur.execute("UPDATE GDN_User " \
                        "SET Permissions = '' " \
                        "WHERE UserID = %i" % (user_id))
            cur.close()
            print "Permission cache for member '%s' deleted." % (name.encode('ascii', 'replace'))
        else:
            no_account.append(name.encode('ascii', 'replace'))

    print "Finished updating users\n"

    if len(no_account) > 0:
        print "The following members' permission cache could not be deleted " \
              "due to not having a forum account: %s\n" % (", ".join(no_account))
                  
# let's do some stuff
update_user_info()
update_categories()
update_roles()
update_user_roles()
update_permissions()
delete_permission_cache()

redcross.close()
vanilla.close()
